/*
 * Created on Aug 22, 2004
 */
package com.scalar.chiptrack.operations.process.dao.gp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.LotCost;

/**
 * @author Ravikanth
**/

public class GPLotDAO
{
	public static void updateLostCostInformationFromGP() throws ScalarException
	{
		Connection conn = null;
		Connection gpConn = null;
		PreparedStatement pstmt = null;
		PreparedStatement gpPstmt = null;
		ResultSet rs = null;
		ArrayList lotList = new ArrayList();
		LotCost lotCost = null; 
		String gpPartNumber = null;
		
		try 
		{
			conn = DataSource.getConnection();
			
			pstmt = conn.prepareStatement ( "select l.Lot_Id, Part_Number, PS_Type, PS_Type_Name, V_Name,( CASE PS_Type_Name WHEN 'FAB' THEN ( Select TOP 1 PurchaseOrder_ID from Fab_lot where Fab_lot.Lot_Id = l.Lot_Id )	WHEN 'SORT' THEN ( Select TOP 1 PurchaseOrder_ID from Sort_lot where Sort_lot.Lot_Id = l.Lot_Id  )	WHEN 'TEST' THEN ( Select TOP 1 PurchaseOrder_ID from Final_test_lot where Final_test_lot.Lot_Id = l.Lot_Id )	WHEN 'Assembly' THEN ( Select TOP 1 PurchaseOrder_ID from Assembly_lot where Assembly_lot.Lot_Id = l.Lot_Id )   END) as PONumber	from Lot_Table l, Product, Process_Step, Vendor, Location where l.ProcessStep_Id = Product.ProcessStep_Id and Process_Step.ProcessStep_Id = Product.ProcessStep_Id and Location.Location_Id = l.Location_Id and Vendor.Vendor_Id = Location.Vendor_ID and PS_Type_Name in ( 'FAB', 'SORT', 'TEST', 'Assembly' )" );
			rs  = pstmt.executeQuery(); 

			while ( rs.next() ) 
			{
				lotCost = new LotCost();
				lotCost.setLot_Id( rs.getInt( "Lot_Id" ) );
				lotCost.setPart_Number( rs.getString( "Part_Number" ) );
				lotCost.setPS_Type( rs.getString( "PS_Type" ) );
				lotCost.setPS_Type_Name( rs.getString( "PS_Type_Name" ) );
				lotCost.setVendor( rs.getString( "V_Name" ) );
				lotCost.setPoNumber( rs.getString( "PONumber" )  );
				
				if( lotCost.getPoNumber() != null )
				{
					lotList.add( lotCost );
				}
			}
			
			rs.close();
			pstmt.close();
			conn.close();
			
			if( lotList != null && lotList.isEmpty() == false )
			{
				gpConn = DataSource.getConnection( Constants.DATASOURCE_TYPE_GREAT_PLAINS );
				
				gpPstmt = gpConn.prepareStatement( "Select UNITCOST from POP10110 where PONUMBER = ? and ITEMNMBR = ?" ); 
				
				for( int i = 0; i < lotList.size(); i++ )
				{
					lotCost = (LotCost)lotList.get( i );
					gpPartNumber = "";
					
					if( lotCost.getPart_Number().length() > 25 )
					{
						gpPartNumber = lotCost.getPart_Number().substring( 0, 25 );
					}
					else
					{
						gpPartNumber = lotCost.getPart_Number();
					}
					
					gpPartNumber = gpPartNumber + '/';

					if( lotCost.getPS_Type().length() > 5 )
					{
						gpPartNumber = gpPartNumber + lotCost.getPS_Type().substring( 0, 5 );
					}
					else
					{
						gpPartNumber = gpPartNumber + lotCost.getPS_Type();
					}
					gpPstmt.setString( 1, lotCost.getPoNumber() );
					gpPstmt.setString( 2, gpPartNumber );
					rs = gpPstmt.executeQuery();
					
					if( rs.next() )
					{
						 lotCost.setLot_Cost( rs.getFloat( "UNITCOST" ) );
					}
					gpPstmt.clearParameters();
				}
			}
			
			rs.close();
			gpPstmt.close();
			gpConn.close();
			
			if( lotList != null && lotList.isEmpty() == false )
			{
				conn = DataSource.getConnection();
				pstmt = conn.prepareStatement( "Update Lot_Table set Lot_Unit_Cost = ? where Lot_Id = ?" ); 
				
				for( int i = 0; i < lotList.size(); i++ )
				{
					lotCost = (LotCost)lotList.get( i );
					if( lotCost.getLot_Cost() > 0 )
					{
						pstmt.setFloat( 1, lotCost.getLot_Cost() );
						pstmt.setInt( 2, lotCost.getLot_Id() );
						pstmt.executeUpdate();
						pstmt.clearParameters();
					}
				}
			}
		}
		catch ( Exception sqle ) 
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not save Lot Cost Information from Great Palins to Chiptrac", sqle);
		}
		finally 
		{
			try 
			{
				if( conn != null ){	conn.commit();	}
			}
			catch (SQLException ignore) {}

			if( rs != null ) 
			{
				try { rs.close(); } catch (SQLException ignore){}
			}

			if( gpPstmt != null ) 
			{
				try { gpPstmt.close(); } catch (SQLException ignore){}
			}

			if( gpConn != null ) 
			{
				try { gpConn.close(); } catch (SQLException ignore){}
			}

			if( pstmt != null ) 
			{
				try { pstmt.close(); } catch (SQLException ignore){}
			}

			if( conn != null ) 
			{
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
	}
}
